﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_siconide_shipping
{
    public DAL_tbl_siconide_shipping()
    {

    }



    public List<tbl_siconide_shipping> select(int ID)
    {
        List<tbl_siconide_shipping> list = new List<tbl_siconide_shipping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Drum_Number,Net_Weight_Shipped,Gross_Weight_Shipped,Shipping_Date,PO_Number,Shipping_Method,Shipping_Vessel,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_shipping where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_shipping()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Drum_Number = dr["Drum_Number"].ToString(),
                Net_Weight_Shipped = Convert.ToDecimal(dr["Net_Weight_Shipped"]),
                Gross_Weight_Shipped = Convert.ToDecimal(dr["Gross_Weight_Shipped"]),
                Shipping_Date = dr["Shipping_Date"].toDateTimeWithoutError(),
                PO_Number = dr["PO_Number"].ToString(),
                Shipping_Method = dr["Shipping_Method"].ToString(),
                Shipping_Vessel = dr["Shipping_Vessel"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_siconide_shipping> selectAll()
    {
        List<tbl_siconide_shipping> list = new List<tbl_siconide_shipping>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Drum_Number,Net_Weight_Shipped,Gross_Weight_Shipped,Shipping_Date,PO_Number,Shipping_Method,Shipping_Vessel,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_shipping Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_shipping()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Drum_Number = dr["Drum_Number"].ToString(),
                Net_Weight_Shipped = Convert.ToDecimal(dr["Net_Weight_Shipped"]),
                Gross_Weight_Shipped = Convert.ToDecimal(dr["Gross_Weight_Shipped"]),
                Shipping_Date = dr["Shipping_Date"].toDateTimeWithoutError(),
                PO_Number = dr["PO_Number"].ToString(),
                Shipping_Method = dr["Shipping_Method"].ToString(),
                Shipping_Vessel = dr["Shipping_Vessel"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select ss.ID,ss.Drum_Number,ss.Net_Weight_Shipped,ss.Gross_Weight_Shipped,
ss.Shipping_Date as Shipping_Date,po.PURCHASE_ORDER_NO as PO_Number,sm.SHIPPING_TYPE as Shipping_Method,ss.Shipping_Vessel
,CASE WHEN ss.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE'
,ss.CREATED_BY,ss.CREATED_DATE as CREATED_DATE,ss.UPDATED_BY,ss.UPDATED_DATE as UPDATED_DATE 
from tbl_siconide_shipping ss LEFT JOIN tbl_shipping_master sm
on sm.SHIPPING_ID=ss.Shipping_Method
LEFT JOIN tbl_purchase_order po ON po.PO_ID = ss.PO_Number Order By ss.ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public int insert(String Drum_Number, Decimal Net_Weight_Shipped, Decimal Gross_Weight_Shipped, DateTime Shipping_Date, String PO_Number, String Shipping_Method, String Shipping_Vessel, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_siconide_shipping(Drum_Number, Net_Weight_Shipped, Gross_Weight_Shipped, Shipping_Date, PO_Number, Shipping_Method, Shipping_Vessel, CREATED_BY, CREATED_DATE)
                        values(@Drum_Number, @Net_Weight_Shipped, @Gross_Weight_Shipped, @Shipping_Date, @PO_Number, @Shipping_Method, @Shipping_Vessel, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@Net_Weight_Shipped", Net_Weight_Shipped);
        cmd.Parameters.AddWithValue("@Gross_Weight_Shipped", Gross_Weight_Shipped);
        cmd.Parameters.AddWithValue("@Shipping_Date", Shipping_Date);
        cmd.Parameters.AddWithValue("@PO_Number", PO_Number);
        cmd.Parameters.AddWithValue("@Shipping_Method", Shipping_Method);
        cmd.Parameters.AddWithValue("@Shipping_Vessel", Shipping_Vessel);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int ID, String Drum_Number, Decimal Net_Weight_Shipped, Decimal Gross_Weight_Shipped, DateTime Shipping_Date, String PO_Number, String Shipping_Method, String Shipping_Vessel, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_siconide_shipping SET Drum_Number=@Drum_Number, Net_Weight_Shipped=@Net_Weight_Shipped, Gross_Weight_Shipped=@Gross_Weight_Shipped, Shipping_Date=@Shipping_Date, PO_Number=@PO_Number, Shipping_Method=@Shipping_Method, Shipping_Vessel=@Shipping_Vessel, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@Net_Weight_Shipped", Net_Weight_Shipped);
        cmd.Parameters.AddWithValue("@Gross_Weight_Shipped", Gross_Weight_Shipped);
        cmd.Parameters.AddWithValue("@Shipping_Date", Shipping_Date);
        cmd.Parameters.AddWithValue("@PO_Number", PO_Number);
        cmd.Parameters.AddWithValue("@Shipping_Method", Shipping_Method);
        cmd.Parameters.AddWithValue("@Shipping_Vessel", Shipping_Vessel);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_siconide_shipping WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}